﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using TaxBusiness.Model;

namespace TaxBusiness.DAL
{
    public class CustomerDB
    {
        private string _insert = "INSERT INTO [Customer]" +
                                      "([TheName]" +
                                      ",[LegalPerson]" +
                                      ",[LPPhone]" +
                                      ",[RegisteredCapital]" +
                                      ",[Profit]" +
                                      ",[AnnualIncome]" +
                                      ",[TaxNumber]"+
                                      ",[FoundDate]" +
                                      ",[Addr]" +
                                      ",[Phone]" +
                                      ",[Contact])" +
                                 "VALUES" +
                                       "(@TheName" +
                                       ",@LegalPerson" +
                                       ",@LPPhone" +
                                       ",@RegisteredCapital" +
                                       ",@Profit" +
                                       ",@AnnualIncome" +
                                       ",@TaxNumber" +
                                       ",@FoundDate" +
                                       ",@Addr" +
                                       ",@Phone" +
                                       ",@Contact)";

        private string _update = "UPDATE [Customer]" +
                           " SET [TheName] = @TheName" +
                              ",[LegalPerson] = @LegalPerson" +
                              ",[LPPhone] = @LPPhone" +
                              ",[RegisteredCapital] = @RegisteredCapital" +
                              ",[Profit] = @Profit" +
                              ",[AnnualIncome] = @AnnualIncome" +
                              ",[TaxNumber]=@TaxNumber" +
                              ",[FoundDate] = @FoundDate" +
                              ",[Addr] = @Addr" +
                              ",[Phone] = @Phone" +
                              ",[Contact] = @Contact" +
                         " WHERE ID=@ID";

        private string _delete = "DELETE FROM [Customer] WHERE ID in @ID";

        private string _select = "SELECT * FROM Customer WHERE ID=@ID";

        private string _selectLike = "SELECT TOP 20 ID,TheName,Contact,Phone,Addr FROM Customer WHERE TheName LIKE '%'+@TheName+'%' ORDER BY TheName";

        private string _selectAll = "select {0} from {1} where {2} order by {3} {4}";

        private string _count = "select count(*) from {0} where {1}";

        private string _selectPagenation = "select {0} from (select (row_number() over(order by {1} {6})) AS rownum,* from {2} where {3}) as tempdb where rownum between '{4}' and '{5}'";

        private string _fielsAll = "*";

        private string _tableName = "[Customer]";

        public int Save(Model.Customer customer)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_insert, customer);
            }
        }

        public int Update(Model.Customer customer)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_update, customer);
            }
        }

        public int Delete(int[] id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_delete, new { ID = id });
            }
        }

        public Customer GetByID(int id)
        {
            Model.Customer customer = new Customer();
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                customer = cn.Query<Customer>(_select, new { ID = id }).Single();
            }
            return customer;
        }

        public List<Customer> GetLike(string customer)
        {
            List<Customer> customerlist = new List<Customer>();
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                customerlist = cn.Query<Customer>(_selectLike, new { TheName = customer }).ToList();
            }
            return customerlist;
        }

        public List<Model.Customer> GetAll(int pageSize, int pageIndex, string whereStr, string order, string keyWord)
        {
            List<Model.Customer> customerlist = new List<Customer>();
            int pageMax = pageIndex * pageSize;
            int pageMin = pageMax - pageSize + 1;
            string sql;
            if (pageIndex > 0)
                sql = string.Format(_selectPagenation, _fielsAll, keyWord, _tableName, whereStr, pageMin, pageMax, order);
            else
                sql = string.Format(_selectAll, _fielsAll, _tableName, whereStr, keyWord, order);
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                customerlist = cn.Query<Model.Customer>(sql).ToList();
            }
            return customerlist;
        }

        public int GetCount(string whereStr)
        {
            string sql = string.Format(_count, _tableName, whereStr);
            int recordCount = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                recordCount = cn.Query<int>(sql).First();
            }
            return recordCount;
        }
    }
}
